package com.shujia.jdbc;

import org.junit.Test;

import java.sql.*;

public class JdbcCrud {

    /*
    解决sql注入的问题
     */
    @Test
    public void loginSafe() {
        // sql 注入
        String userName = "张三";
        String password = "111111";

        try {
            // 不拼接sql, 使用?(占位符)表示一个参数 ,
            String sql = "select * from t_user where user_name = ? and password = md5(?)";

            Connection connection = JdbcUtil.getConnection();
            // 预编译sql语句, ? 仅仅表示一个参数, 不参与sql逻辑
            PreparedStatement ps = connection.prepareStatement(sql);

            // 填充占位符
            ps.setString(1, userName);
            ps.setObject(2, password);

            // 获取查询的结果集
            ResultSet rs = ps.executeQuery();

            boolean flag = rs.next();
            if (flag) {
                System.out.println("登录成功");
            } else {
                System.out.println("登录失败");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    /*
    查询用户的列表
     */
    @Test
    public void queryUserList() {
        Connection connection = null;
        Statement statement = null;
        try {
            String sql = "select * from t_user";

            connection = JdbcUtil.getConnection();
            statement = connection.createStatement();

            // 获取查询的结果集
            ResultSet rs = statement.executeQuery(sql);

            while (rs.next()) {// 判断是否有下一行数据
                // 获取本行数据的值
//                int id = rs.getInt("id");
//                String userName = rs.getString("user_name");
//                Timestamp birth = rs.getTimestamp("birth");
//                String address = rs.getString("address");

                int id = rs.getInt(1);
                String userName = rs.getString(2);
                Timestamp birth = rs.getTimestamp(4);
                String address = rs.getString(5);


                System.out.println("id:" + id + ",username:" + userName +
                        ",birth:" + birth.toString() + ",address:" + address);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtil.close(connection, statement);
        }
    }

    /*
    登录功能
     */
    @Test
    public void queryUser() {
        // sql 注入
        String userName = "'张三' or 1 = 1";
        String password = "111111";

        try {
            String sql = "select * from t_user where user_name = " + userName + " " +
                    "and  password = md5(" + password + ")";

            Connection connection = JdbcUtil.getConnection();
            Statement statement = connection.createStatement();

            // 获取查询的结果集
            ResultSet rs = statement.executeQuery(sql);

            boolean flag = rs.next();

            if (flag) {
                System.out.println("登录成功");
            } else {
                System.out.println("登录失败");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    @Test
    public void delete() {
        int id = 3;
        String sql = "delete from t_user where id = " + id;
        updateRecord(sql);
    }

    @Test
    public void update() {
        int id = 3;
        String pwd = "111111";
        String address = "上海";
        String sql = "update t_user set password = md5(" + pwd + "), address = '" + address
                + "' where id = " + id;

        int i = updateRecord(sql);
        System.out.println(i);
    }


    /*
    封装的增删改通用方法
     */
    public int updateRecord(String sql) {
        Connection connection = null;
        Statement statement = null;
        try {
            connection = JdbcUtil.getConnection();
            statement = connection.createStatement();

            int i = statement.executeUpdate(sql);
            return i;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 关闭资源
            JdbcUtil.close(connection, statement);
        }
        return 0;
    }


    // 用户修改密码和地址
    @Test
    public void updateUser() {
        Connection connection = null;
        Statement statement = null;
        try {
            String sql = "update t_user set password = md5(111111), address = '上海' where id = 3";
            connection = JdbcUtil.getConnection();
            statement = connection.createStatement();

            int i = statement.executeUpdate(sql);
            System.out.println(i);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 关闭资源
            JdbcUtil.close(connection, statement);
        }


    }


    //实现注册功能
    @Test
    public void registerTest() throws SQLException {
        // 获取连接
        Connection connection = JdbcUtil.getConnection();

        String sql = "insert into t_user(user_name, `password`, birth, address) " +
                "values('王五', md5('123456') , '2012-09-20 15:16:19', '广东')";

        //3.获取数据库操作对象（专门执行sql语句的对象）
        Statement statement = connection.createStatement();

        //4.执行SQL语句（DQL,DML…）, 返回sql印象的数据的条数
        int i = statement.executeUpdate(sql);

        System.out.println("影响了" + i + "行数据");

        //5.处理查询结果集 （只有当第四步执行的是select语句的时候，才有本步）


        //6.释放资源（使用完资源后一定要关闭资源，Java和数据库之间属于进程间的通信，开启之后一定要记得关闭）
//        statement.close();
//        connection.close();
        JdbcUtil.close(connection, statement);
    }

}
